﻿using System.Data;
using System.Threading.Tasks;
using Dapper;
using Entity;
using IRepository;

namespace Repository
{
    public class ProductInformationRepository : IProductInformationRepository
    {
        private readonly IDbConnection _dbConnection;

        public ProductInformationRepository(IDbConnection dbConnection)
        {
            _dbConnection = dbConnection;
        }

        public async Task<ProductInformationEntity> GetByCodeAsync(string productCode, IDbTransaction transaction)
        {
            var sql = "SELECT * FROM wms_basic_product_information WHERE product_code = @Code AND is_delete = '0'";
            return await _dbConnection.QueryFirstOrDefaultAsync<ProductInformationEntity>(
                sql,
                new { Code = productCode },
                transaction
            );
        }
        // 实现分页搜索
        public async Task<(List<ProductInformationEntity> list, int total)> SearchAsync(
            string keyword, string unit, int pageNum, int pageSize)
        {
            // 构建查询条件
            var conditions = new List<string>();
            var parameters = new DynamicParameters();

            // 基础条件：未删除（假设存在is_delete字段）
            conditions.Add("is_delete = 0");

            // 关键词搜索（编号或名称）
            if (!string.IsNullOrEmpty(keyword))
            {
                conditions.Add("(product_code LIKE @Keyword OR product_name LIKE @Keyword)");
                parameters.Add("Keyword", $"%{keyword}%");
            }

            // 单位筛选
            if (!string.IsNullOrEmpty(unit))
            {
                conditions.Add("unit = @Unit");
                parameters.Add("Unit", unit);
            }

            // 拼接WHERE条件
            var whereSql = conditions.Any() ? $"WHERE {string.Join(" AND ", conditions)}" : "";

            // 总条数查询
            var totalSql = $"SELECT COUNT(1) FROM wms_basic_product_information {whereSql}";
            var total = await _dbConnection.ExecuteScalarAsync<int>(totalSql, parameters);

            // 分页数据查询
            var offset = (pageNum - 1) * pageSize;
            var dataSql = $@"
                SELECT * FROM wms_basic_product_information 
                {whereSql}  
                ORDER BY create_time DESC
                OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

            parameters.Add("Offset", offset);
            parameters.Add("PageSize", pageSize);

            var list = (await _dbConnection.QueryAsync<ProductInformationEntity>(dataSql, parameters)).ToList();

            return (list, total);
        }
        // 带仓库库存的搜索（核心修改：关联库存表查询）
        // Repository/ProductInformationRepository.cs
        public async Task<(List<ProductInformationEntity> list, int total)> SearchWithStockAsync(
            string? keyword, int outWarehouseId, int inWarehouseId,
            int pageNum, int pageSize)
        {
            // 参数校验（保持不变）
            pageNum = Math.Max(pageNum, 1);
            pageSize = Math.Clamp(pageSize, 1, 100);
            if (outWarehouseId <= 0)
                throw new ArgumentException("调出仓库ID必须大于0", nameof(outWarehouseId));
            if (inWarehouseId <= 0)
                throw new ArgumentException("调入仓库ID必须大于0", nameof(inWarehouseId));

            // 构建查询条件
            var conditions = new List<string> { "p.is_delete = 0" };
            var parameters = new DynamicParameters();
            parameters.Add("OutWarehouseId", outWarehouseId);
            parameters.Add("InWarehouseId", inWarehouseId);

            if (!string.IsNullOrWhiteSpace(keyword))
            {
                conditions.Add("(p.product_code LIKE @Keyword OR p.product_name LIKE @Keyword)");
                parameters.Add("Keyword", $"%{keyword}%");
            }

            var whereSql = conditions.Any() ? $"WHERE {string.Join(" AND ", conditions)}" : "";

            // 1. 查询总条数（修正表名：wms_inventory → wms_knzy_inventory）
            var totalSql = $@"
        SELECT COUNT(DISTINCT p.product_id) 
        FROM wms_basic_product_information p
        LEFT JOIN wms_knzy_inventory ow ON p.product_code = ow.product_code AND ow.warehouse_id = @OutWarehouseId
        LEFT JOIN wms_knzy_inventory iw ON p.product_code = iw.product_code AND iw.warehouse_id = @InWarehouseId
        {whereSql}";
            var total = await _dbConnection.ExecuteScalarAsync<int>(totalSql, parameters);

            // 2. 分页查询数据（修正表名和库存字段：stock_quantity → current_inventory）
            var offset = (pageNum - 1) * pageSize;
            var dataSql = $@"
        SELECT DISTINCT
            p.*,
            ISNULL(ow.current_inventory, 0) AS out_warehouse_stock,  -- 库存字段修正为current_inventory
            ISNULL(iw.current_inventory, 0) AS in_warehouse_stock    -- 库存字段修正为current_inventory
        FROM wms_basic_product_information p
        -- 关联调出仓库库存（表名修正为wms_knzy_inventory）
        LEFT JOIN wms_knzy_inventory ow 
            ON p.product_code = ow.product_code AND ow.warehouse_id = @OutWarehouseId
        -- 关联调入仓库库存（表名修正为wms_knzy_inventory）
        LEFT JOIN wms_knzy_inventory iw 
            ON p.product_code = iw.product_code AND iw.warehouse_id = @InWarehouseId
        {whereSql}
        ORDER BY p.create_time DESC
        OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

            parameters.Add("Offset", offset);
            parameters.Add("PageSize", pageSize);

            var list = (await _dbConnection.QueryAsync<ProductInformationEntity>(dataSql, parameters))
                .ToList();

            return (list, total);
        }

        /// <summary>
        /// 进货明细添加的查询
        /// </summary>
        /// <param name="code">编号</param>
        /// <param name="Unit">单位</param>
        /// <returns></returns>
        /// <exception cref="NotImplementedException"></exception>
        public async Task<List<ProductInformationFind>> GetByCodeAsync(string? code = null, string? Unit = null)
        {
            string sql = @"select * from wms_basic_product_information wbi 
                           inner join wms_knzy_inventory wki
                           on wbi.product_code = wki.product_code
                           where 1 = 1 ";
            if (code != null) 
            {
                code = "%" + code + "%";
                sql += " and wbi.product_code like @Code ";
            }

            if (Unit != null)
            {
                sql += " and unit = @Unit ";
            }
            
            return (await _dbConnection.QueryAsync<ProductInformationFind>(sql, new { Code = code, unit = Unit })).AsList();
        }
    }
}